import mysql.connector
from mysql.connector import Error
import pandas as pd
from openpyxl import Workbook


def export_db_structure_to_excel(host, user, password, database, output_file):
    try:
        # 连接MySQL数据库
        conn = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )

        # 获取所有表名
        cursor = conn.cursor()
        cursor.execute(f"""
            SELECT TABLE_NAME 
            FROM INFORMATION_SCHEMA.TABLES 
            WHERE TABLE_SCHEMA = '{database}' 
            AND TABLE_TYPE = 'BASE TABLE'
        """)
        tables = [table[0] for table in cursor.fetchall()]

        # 创建Excel writer对象
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            for table in tables:
                # 查询表结构
                query = f"""
                    SELECT
                        COLUMN_NAME AS '列名',		
                        COLUMN_COMMENT AS '描述',
                        DATA_TYPE AS '类型',
                        CHARACTER_MAXIMUM_LENGTH AS '长度',
                        IFNULL(COLUMN_KEY,DECODE2(IS_NULLABLE,'YES','NOT NULL','')) AS '约束',
                        '' AS '备注',
                        '' AS '主键计算',
                        '' AS '联合唯一值'
                    FROM
                        INFORMATION_SCHEMA.COLUMNS
                    WHERE
                        TABLE_SCHEMA = '{database}'
                        AND TABLE_NAME = '{table}'
                    ORDER BY ORDINAL_POSITION
                """
                df = pd.DataFrame
                df = pd.read_sql(query, conn)

                # 处理Sheet名称（Excel限制：最大31字符，不能包含特殊字符）
                sheet_name = table[:31].replace('*', '').replace(':', '').replace('?', '').replace('/', '')

                # 写入Excel
                df.to_excel(
                    writer,
                    sheet_name=sheet_name,
                    index=False,
                    header=True
                )

                # 自动调整列宽
                worksheet = writer.sheets[sheet_name]
                for column in worksheet.columns:
                    max_length = 0
                    column = [cell for cell in column]
                    try:
                        max_length = max(len(str(cell.value)) for cell in column)
                    except:
                        pass
                    adjusted_width = (max_length + 2)
                    worksheet.column_dimensions[column[0].column_letter].width = adjusted_width

        print(f"导出成功！文件已保存至：{output_file}")

    except Error as e:
        print(f"数据库连接错误: {e}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()


# 使用示例
if __name__ == "__main__":
    export_db_structure_to_excel(
        host="123.56.171.51",  # 数据库地址
        user="root",  # 数据库用户名
        password="lgmi6395",  # 数据库密码
        database="xxl_job",  # 数据库名称
        output_file="../job_database_structure.xlsx"  # 输出文件名
    )